/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples.enhancedads.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Catalog maps the information for each item of the inventory into the local
 * database.
 * 
 * @author rodrigo.f.vale@gmail.com
 */
public class Catalog {
  private Connection conn;
  private PreparedStatement insert; 
  public Catalog(Connection conn) {
    this.conn = conn;
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS Catalog "
        + "  ( "
        + "     ProdId          STRING, "          
        + "     Name        STRING, "        
        + "     Price           FLOAT, "
        + "     RegularPrice    FLOAT, "
        + "     Url             STRING, "
        + "     Image             STRING, "
        + "     CategoryId           STRING, "
        + "     Parcel          INTEGER, "
        + "     ParcelValue     FLOAT, "
        + "     Author          STRING, "
        + "     Other           STRING, "
        + "     Units           INTEGER, "
        + "     Discount        INTEGER, "
        + "     PRIMARY KEY (ProdId, CategoryId)"
        + "  ); ");
      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS CatalogCategoryIdPriceIndex " 
        + "on Catalog (CategoryId, Price);");      

      insert = conn.prepareStatement(
          "INSERT INTO Catalog (ProdId, Name, Price, RegularPrice," +
          "Url, Image, CategoryId, Parcel, ParcelValue, Author, Other, " +
          "Units, Discount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void clear() {
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate("DELETE FROM Catalog");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void saveBatch(String prodId, String prodName, float price, 
      float regularPrice, String url, String img, String catId, int parcel,
      float parcelValue, String author, String other, int units,
      int discount) {
    try {
      insert.setString(1, prodId);
      insert.setString(2, prodName);
      insert.setFloat(3, price);
      insert.setFloat(4, regularPrice);
      insert.setString(5, url);
      insert.setString(6, img);
      insert.setString(7, catId);
      insert.setInt(8, parcel);
      insert.setFloat(9, parcelValue);
      insert.setString(10, author);
      insert.setString(11, other);
      insert.setInt(12, units);
      insert.setInt(13, discount);
      insert.addBatch();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void executeBatch() {
    try {
      insert.executeBatch();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}
